ALTER TABLE NodeType
ADD UNIQUE(Name)
GO

ALTER TABLE Customer
ADD Phone VARCHAR(20) NULL
GO

ALTER TABLE Customer
ADD Description VARCHAR(200) NULL
GO

ALTER TRIGGER TR_UPDATE_LineSegment ON LineSegment
INSTEAD OF UPDATE AS
BEGIN
IF (UPDATE(AssetKey) OR UPDATE(Description) OR UPDATE (AssetName) OR UPDATE(VoltageKV) OR Update(Spare))
	BEGIN
		UPDATE Asset
		SET
			Asset.AssetKey = INSERTED.AssetKey,
			Asset.Description = INSERTED.Description,
			Asset.AssetName = INSERTED.AssetName,
			Asset.VoltageKV  = INSERTED.VoltageKV,
			Asset.Spare = INSERTED.Spare
		FROM
			ASSET 
		INNER JOIN
			INSERTED
		ON 
			INSERTED.ID = ASSET.ID;
	END
	UPDATE LineSegmentAttributes
		SET
			LineSegmentAttributes.R0 = INSERTED.R0,
			LineSegmentAttributes.X0 = INSERTED.X0,
			LineSegmentAttributes.R1 = INSERTED.R1,
			LineSegmentAttributes.X1 = INSERTED.X1,
            LineSegmentAttributes.Length = INSERTED.Length,
			LineSegmentAttributes.ThermalRating = INSERTED.ThermalRating,
            LineSegmentAttributes.IsEnd = INSERTED.IsEnd
		FROM
			LineSegmentAttributes 
	INNER JOIN
		INSERTED
	ON 
		INSERTED.ID = LineSegmentAttributes.AssetID;
END
GO

INSERT INTO DataOperation(AssemblyName, TypeName, LoadOrder) VALUES('FaultData.dll', 'FaultData.DataOperations.RelayEnergization', 13)
GO

ALTER TABLE UserAccount
ADD TSCID INT NULL
GO

ALTER TABLE UserAccount
ADD RoleID INT NULL
GO

ALTER TABLE UserAccount
ADD Title VARCHAR(200) NULL
GO

ALTER TABLE UserAccount
ADD Department VARCHAR(200) NULL
GO

ALTER TABLE UserAccount
ADD DepartmentNumber VARCHAR(200) NULL
GO

ALTER TABLE UserAccount
ADD MobilePhone VARCHAR(200) NULL
GO

ALTER TABLE UserAccount
ADD ReceiveNotifications BIT NOT NULL DEFAULT 1
GO

CREATE TABLE CompanyType(
	ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name VARCHAR(200) NOT NULL UNIQUE,
	Description VARCHAR(MAX) NULL
)
GO

CREATE TABLE Company
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CompanyID VARCHAR(8) NOT NULL UNIQUE,
    CompanyTypeID int NOT NULL REFERENCES CompanyType(ID),
    Name VARCHAR(200) NOT NULL,
    Description VARCHAR(MAX) NULL
)
GO

CREATE TABLE CompanyMeter
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CompanyTypeID INT NOT NULL REFERENCES Company(ID),
    MeterID INT NOT NULL REFERENCES Meter(ID),
    DisplayName VARCHAR(200) NOT NULL,
    Enabled BIT NOT NULL
)
GO

DECLARE @pqMarkCompanyIDConstraint VARCHAR(MAX) =
(
    SELECT CONSTRAINT_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
        TABLE_NAME = 'PQMarkCompanyCustomer' AND
        COLUMN_NAME = 'PQMarkCompanyID'
)

DECLARE @stmt NVARCHAR(MAX) =
    N'ALTER TABLE PQMarkCompanyCustomer DROP CONSTRAINT ' + @pqMarkCompanyIDConstraint

EXEC sp_executesql @stmt
GO

ALTER TABLE PQMarkCompanyCustomer
ADD FOREIGN KEY(PQMarkCompanyID) REFERENCES Company(ID)
GO

DROP INDEX IX_PQMarkCompanyMeter_MeterID
ON PQMarkCompanyMeter
GO

CREATE NONCLUSTERED INDEX IX_PQMarkCompanyMeter_MeterID 
ON CompanyMeter(MeterID) 
GO

ALTER VIEW AssetGroupView AS
SELECT
    AssetGroup.ID,
    AssetGroup.Name,
    AssetGroup.DisplayDashboard,
    AssetGroupAssetGroup.Count AS AssetGroups,
    MeterAssetGroup.Count AS Meters,
    AssetAssetGroup.Count AS Assets,
    UserAccountAssetGroup.Count AS Users
FROM
    AssetGroup OUTER APPLY
    (SELECT COUNT(*) FROM AssetGroupAssetGroup WHERE AssetGroup.ID = AssetGroupAssetGroup.ParentAssetGroupID) AssetGroupAssetGroup(Count) OUTER APPLY
    (SELECT COUNT(*) FROM MeterAssetGroup WHERE AssetGroup.ID = MeterAssetGroup.AssetGroupID) MeterAssetGroup(Count) OUTER APPLY
    (SELECT COUNT(*) FROM AssetAssetGroup WHERE AssetGroup.ID = AssetAssetGroup.AssetGroupID) AssetAssetGroup(Count) OUTER APPLY
    (SELECT COUNT(*) FROM UserAccountAssetGroup WHERE AssetGroup.ID = UserAccountAssetGroup.AssetGroupID) UserAccountAssetGroup(Count)
GO

ALTER VIEW BreakerHistory
AS
SELECT  Breaker.ID AS BreakerID,
		RelayPerformance.EventID AS EventID,
        RelayPerformance.Imax1,
		RelayPerformance.Imax2,
		RelayPerformance.TripInitiate,
		RelayPerformance.TripTime / 10 AS TripTime,
		RelayPerformance.PickupTime / 10 AS PickupTime,
		RelayPerformance.TripCoilCondition,
		Breaker.TripCoilCondition AS TripCoilConditionAlert,
		Breaker.TripTime AS TripTimeAlert,
		Breaker.PickupTime AS PickupTimeAlert,
		RelayPerformance.ChannelID AS TripCoilChannelID
FROM    RelayPerformance LEFT OUTER JOIN
        Channel ON RelayPerformance.ChannelID = Channel.ID LEFT OUTER JOIN
        Breaker ON Breaker.ID = Channel.AssetID
GO

CREATE TABLE [ValueListGroup](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) NULL,
	[Description] [varchar](max) NULL,
)

CREATE TABLE [ValueList](
    [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [GroupID] [int] NOT NULL FOREIGN KEY REFERENCES ValueListGroup(ID),
    [Text] [varchar](200) NULL,
    [Value] [int] NULL,
    [Description] [varchar](max) NULL,
    [SortOrder] [int] NULL,
)
GO


CREATE TABLE [SystemCenter.AdditionalField] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	ParentTable varchar(100) NOT NULL,
	FieldName varchar(100) NOT NULL,
	Type varchar(max) NULL DEFAULT ('string'),
	ExternalDB varchar(max) NULL,
	ExternalDBTable varchar(max) NULL,
	ExternalDBTableKey varchar(max) NULL,
	IsSecure bit NULL DEFAULT(0)
	Constraint UC_AdditonaField UNIQUE(ParentTable, FieldName)
)
GO

CREATE TABLE [SystemCenter.AdditionalFieldValue] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	ParentTableID int NOT NULL,
	AdditionalFieldID int NOT NULL FOREIGN KEY REFERENCES [SystemCenter.AdditionalField](ID),
	Value varchar(max) NULL,
    UpdatedOn DATE NULL DEFAULT (SYSDATETIME()),
	Constraint UC_AdditonaFieldValue UNIQUE(ParentTableID, AdditionalFieldID)
)
GO

CREATE TABLE [SystemCenter.ExternalOpenXDAField] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	ParentTable varchar(100) NOT NULL,
	FieldName varchar(100) NOT NULL,
	ExternalDB varchar(max) NULL,
	ExternalDBTable varchar(max) NULL,
	ExternalDBTableKey varchar(max) NULL,
	Constraint UC_ExternalOpenXDAField UNIQUE(ParentTable, FieldName)
)
GO

CREATE Table [SystemCenter.extDBTables] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	TableName varchar(200) NOT NULL,
    ExternalDB varchar(200) NOT NULL,
	Query varchar(max) NULL,
)
GO

CREATE TABLE [SystemCenter.TSC] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(200) NOT NULL,
	Description varchar(max) NULL,
	DepartmentNumber varchar(6) NOT NULL
)
GO

CREATE TABLE [SystemCenter.CustomerAccess] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	CustomerID int NOT NULL FOREIGN KEY REFERENCES Customer(ID),
	PQViewSiteID int NOT NULL
)
GO

CREATE TABLE [SystemCenter.CustomerAccessPQDigest] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	CustomerID int NOT NULL FOREIGN KEY REFERENCES Customer(ID),
	OpenXDAMeterID INT FOREIGN KEY REFERENCES Meter(ID)
)
GO

CREATE TABLE [SystemCenter.Role] (
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(200) NOT NULL,
	Description varchar(max) NULL,
)
GO